﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using Earthwatchers.Models;

namespace Earthwatchers.Data
{
    public class CommentRepository : ICommentRepository
    {
        private readonly IDbConnection connection;

        public CommentRepository(string connectionString)
        {
            connection = new SqlConnection(connectionString);
        }

        public List<Comment> GetCommentsByLand(int id)
        {
            connection.Open();
            var comments = connection.Query<Comment>("select a.Id as Id,a.EarthwatcherId as EarthwatcherId, a.LandId as LandId,a.UserComment as UserComment,a.Published as Published, l.Name as UserName from Comments a left join Earthwatcher l on a.EarthwatcherId=l.Id where a.LandId=@Id order by a.Published Desc", new { Id = id });
            connection.Close();
            return comments.ToList();
        }

        public List<Comment> GetCommentsByUserId(int id)
        {
            connection.Open();
            var comments = connection.Query<Comment>("select a.Id as Id,a.EarthwatcherId as EarthwatcherId, a.LandId as LandId,a.UserComment as UserComment,a.Published as Published, l.Name as UserName from Comments a left join Earthwatcher l on a.EarthwatcherId=l.Id where a.EarthwatcherId=@Id order by a.Published Desc", new { Id = id });
            connection.Close();
            return comments.ToList();
        }

        public Comment PostComment(Comment comment)
        {
            connection.Open();
            var cmd = connection.CreateCommand();
            cmd.CommandText = "insert into Comments(EarthwatcherId, LandId, UserComment, Published) values(@userid,@landid,@Comment,@Published) SET @ID = SCOPE_IDENTITY()";
            cmd.Parameters.Add(new SqlParameter("@userid", comment.EarthwatcherId));
            cmd.Parameters.Add(new SqlParameter("@landid", comment.LandId));
            cmd.Parameters.Add(new SqlParameter("@Comment", comment.UserComment));
            cmd.Parameters.Add(new SqlParameter("@Published", DateTime.Now.ToUniversalTime()));
            var idParameter = new SqlParameter("@ID", SqlDbType.Int) {Direction = ParameterDirection.Output};
            cmd.Parameters.Add(idParameter);
            cmd.ExecuteNonQuery();
            var id = (int)idParameter.Value;
            comment.Id = id;
            connection.Close();
            return comment;
        }

        public void DeleteComment(int commentId)
        {
            connection.Open();
            const string sql = "delete from Comments where Id=@Id";
            var command = connection.CreateCommand();
            command.CommandText = sql;            
            command.Parameters.Add(new SqlParameter("@Id", commentId));
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}
